import requests
from lxml import etree
import lxml
import pymysql
import openpyxl as op

db = pymysql.connect(host='localhost',user='root',password='a159357a*',port=3306,charset='utf8',db="law")


url = 'https://www.spp.gov.cn/guestweb/s?siteCode=N000005434&searchWord=%E7%BD%91%E7%BB%9C%E7%88%AC%E8%99%AB'

headers = {
            "Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
            "Accept-Encoding":"gzip, deflate, br",
            "Accept-Language":"zh-CN,zh;q=0.9",
            "Cache-Control":"no-cache",
            "Connection":"keep-alive",
            "Cookie":"JSESSIONID=FC9AC0E39ADDBC4C9FC22D67A006B97C; TS01c20281=01a6b27f0845ab92e7534cbfabf69f7103b98d5abc0ebf31113f18b25b5326057e52a25fb688645153cc7678d6bf90df2e8be38d67; firstWord=%u7F51%u7EDC%u722C%u866B; userSearch=siteCode-N000005434&column-%E5%85%A8%E9%83%A8&uc-0&firstWord-%E7%BD%91%E7%BB%9C%E7%88%AC%E8%99%AB&searchWord-%E7%BD%91%E7%BB%9C%E7%88%AC%E8%99%AB; TS0184b316=01a6b27f0845ab92e7534cbfabf69f7103b98d5abc0ebf31113f18b25b5326057e52a25fb688645153cc7678d6bf90df2e8be38d67; TSd84ad2c7027=0886aacbbeab2000176d8ee6e894ef2d6999ccea69a618923fdb18cf958f190e45a1be3d1b22e8660891a8c245113000a090311fa9d857101df2d33a1a64de8b3b510794b0cc0eeeff55a4cb70e6ea60f9c97d86fa25ac55944176b69f4bf268",
            "Host":"www.spp.gov.cn",
            "Pragma":"no-cache",
            "Upgrade-Insecure-Requests":"1",
            "User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36"

}

response = requests.get(url=url,headers=headers)
data=response.content.decode()
html=etree.HTML(data)
types = html.xpath('//*/div[1]/a/text()')
titles = html.xpath('//*/div[1]/h3/a/@title')


"""
//*[@id="0"]/div[1]/a
//*[@id="1"]/div[1]/a
//*[@id="0"]/div[1]/h3/a
"""
cursor = db.cursor()
# cursor.execute("create database law;")
# cursor.execute("use law;")
# cursor.execute("create table laws(ty varchar(255),titles varchar(255));")

ws = op.Workbook()  # 创建workbook

wb = ws.create_sheet(index=0)  # 创建worsheetz

wb.cell(row=1, column=1, value='类型')
wb.cell(row=1, column=2, value='标题详情')

# 加入count是为了换行写入数据
count = 2

for i in range(10):
    ty=str(types[i + 1])
    # print(ty)

    # print(titles[i])
    item = {"ty": ty,
            "titles":titles[i]
            }
    print(item)
    type1 = item['ty']
    title1 = item['titles']
    print(type1)
    print(title1)
    sql = f"insert into laws(ty,titles) values ('{type1}','{title1}')"

    cursor.execute(sql)
    db.commit()



    # 要写入excel的数据
    type1 = item['ty']  # 类型
    title1 = item['titles']  # 标题详情

    # 将数据写入到下一行
    wb.cell(row=count, column=1, value=type1)
    wb.cell(row=count, column=2, value=title1)

    # count加1，进入到下一行写入数据
    count += 1
db.close()
ws.save('law.xlsx')








